package com.manage.dao.impl;

import com.manage.dao.BuildingDao;
import com.manage.pojo.Building;
import com.manage.pojo.Housing;
import com.manage.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BuildingDaoImpl implements BuildingDao {
    @Override
    public List<Building> findAll() throws SQLException {
        QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
        List<Building> query = qr.query(" SELECT b.bid,h.hname,b.bnumber,b.bname,COUNT(r.rid)AS rcount,b.bdescribe,b.bhiredate FROM building AS b LEFT JOIN housing AS h ON b.hid=h.hid LEFT JOIN room AS r ON b.bid=r.bid GROUP BY b.bid",  new ResultSetHandler<List<Building>>() {
            @Override
            public List<Building> handle(ResultSet resultSet) throws SQLException {
                List<Building> list=new ArrayList<Building>();
                while (resultSet.next()){
                    int bid = Integer.parseInt(resultSet.getString(1));
                    String hname = resultSet.getString(2);
                    String bnumber = resultSet.getString(3);
                    String bname = resultSet.getString(4);
                    int rcount =  Integer.parseInt(resultSet.getString(5));
                    String bdescribe = resultSet.getString(6);
                    Date bhiredate = resultSet.getDate(7);
                    Housing housing=new Housing();
                    housing.setHname(hname);
                    Building building=new Building(bid,housing,bnumber,bname,rcount,bdescribe,bhiredate);
                    list.add(building);
                }
                return list;
            }

        });
        return query;
    }

    @Override
    public List<Building> findAll(int page, int limit) throws SQLException {
        QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
        Object[] args={(page-1)*limit,limit};
        List<Building> query = qr.query(" SELECT b.bid,h.hname,b.bnumber,b.bname,COUNT(r.rid)AS rcount,b.bdescribe,b.bhiredate FROM building AS b LEFT JOIN housing AS h ON b.hid=h.hid LEFT JOIN room AS r ON b.bid=r.bid GROUP BY b.bid limit ?,?", new ResultSetHandler<List<Building>>() {
            @Override
            public List<Building> handle(ResultSet resultSet) throws SQLException {
                List<Building> list=new ArrayList<Building>();
                while (resultSet.next()){
                    int bid = Integer.parseInt(resultSet.getString("bid"));
                    String hname = resultSet.getString("hname");
                    String bnumber = resultSet.getString("bnumber");
                    String bname = resultSet.getString("bname");
                    int rcount =  Integer.parseInt(resultSet.getString("rcount"));
                    String bdescribe = resultSet.getString("bdescribe");
                    Date bhiredate = resultSet.getDate("bhiredate");
                    Housing housing=new Housing();
                    housing.setHname(hname);
                    Building building=new Building(bid,housing,bnumber,bname,rcount,bdescribe,bhiredate);
                    list.add(building);
                }
                return list;
            }

        }, args);
        return query;
    }

    @Override
    public int deleteBuildingByHid(int hid) throws SQLException {
        QueryRunner qr=new QueryRunner();


            String sql="DELETE FROM building WHERE hid=?";
            int update = qr.update(JDBCUtils.getConn(), sql, hid);
            return update;
    }

    @Override
    public int deleteBuildingByBid(int bid) throws SQLException {
        QueryRunner qr=new QueryRunner();

        JDBCUtils.begin();
        String sql="DELETE FROM building WHERE bid=?";
        int update = qr.update(JDBCUtils.getConn(), sql, bid);

        return update;

    }
    @Override
    public int insertBuilding(Building building) throws SQLException {
        QueryRunner qr=new QueryRunner();
        Object[] args = {building.getHid(),building.getBnumber(),building.getBname(),building.getBdescribe(),building.getBhiredate()};
        int update = qr.update(JDBCUtils.getConn(), "insert into building values (null,?,?,?,?,?)", args);
        return update;
    }
}
